/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import java.sql.*;
import java.util.Vector;

public class Vender 
{
    private int VenderId;
    private String VenderName;
    private String Address;
    private String Email;
    private String  Phone;

    /**
     * @return the VenderId
     */
    public int getVenderId() {
        return VenderId;
    }

    /**
     * @param VenderId the VenderId to set
     */
    public void setVenderId(int VenderId) {
        this.VenderId = VenderId;
    }
    
    /**
     * @return the VandorName
     */
    public String getVenderName() {
        return VenderName;
    }

    /**
     * @param VandorName the VandorName to set
     */
    public void setVenderName(String VenderName) {
        this.VenderName = VenderName;
    }

    /**
     * @return the Address
     */
    public String getAddress() {
        return Address;
    }

    /**
     * @param Address the Address to set
     */
    public void setAddress(String Address) {
        this.Address = Address;
    }

    /**
     * @return the Email
     */
    public String getEmail() {
        return Email;
    }

    /**
     * @param Email the Email to set
     */
    public void setEmail(String Email) {
        this.Email = Email;
    }

    /**
     * @return the Phone
     */
    public String getPhone() {
        return Phone;
    }

    /**
     * @param Phone the Phone to set
     */
    public void setPhone(String Phone) {
        this.Phone = Phone;
    }
    
    public static Vector VenderPT(int a)
    {
        Vector vp = new Vector();
        int b = (a * 5) - 4;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "declare @countl int; set @countl = (select COUNT(*) from Vender); SELECT * FROM (SELECT * , ROW_NUMBER() OVER(ORDER BY VenderId  DESC) RN FROM Vender WHERE VenderId BETWEEN 0 AND @countl) a  WHERE RN BETWEEN '" + b + "' AND '" + (a * 5) + "' ORDER BY VenderId ";
            PreparedStatement ps = con.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) 
            {
                Vector vt = new Vector();
                vt.add(rs.getInt("VenderId"));
                vt.add(rs.getString("VenderName"));
                vt.add(rs.getString("Address"));
                vt.add(rs.getString("Email"));
                vt.add(rs.getString("Phone"));
                vp.add(vt);
            }
        } 
        catch (Exception ex) 
        {
            vp = null;
            System.out.println("loi:"+ex);
        }
        return vp;
    }
    
        public static   Vector ListVander3(String vendername)
    {
        Vector Vender = new  Vector(); 
        try
        {//select * from Vender where VanderName like '%"+this.'
            Connection con = ConecttoDatabase.getConnection();
            String query = "select * from Vender where VenderName like'%"+vendername+"%' ";
            Statement st=con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Vector vt = new Vector();
                vt.add(rs.getInt("VenderId"));
                vt.add(rs.getString("VenderName"));
                vt.add(rs.getString("Address"));
                vt.add(rs.getString("Email"));
                vt.add(rs.getString("Phone"));
                Vender.add(vt);
            }
        }
        catch(Exception ex)
        {
            Vender=null;
        }
        
        return Vender;
    }
    
    public static  Vector ListVander()
    {
        Vector Vender = new  Vector(); 
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select * from Vender ";
            Statement st=con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Vector vt = new Vector();
                vt.add(rs.getInt("VenderId"));
                vt.add(rs.getString("VenderName"));
                vt.add(rs.getString("Address"));
                vt.add(rs.getString("Email"));
                vt.add(rs.getString("Phone"));
                Vender.add(vt);
            }
        }
        catch(Exception ex)
        {
            Vender=null;
        }
        
        return Vender;
    }
    
    
    public  Vector SearchVender(String vendername)
    {
        Vector Vender = new  Vector(); 
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select * from Vender where VenderName like '%"+ vendername +"%'";
            Statement st=con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Vender vd = new Vender();
               
                vd.setVenderName(rs.getString("VenderName"));
            
                Vender.add(vd);
                
                
            }
        }
        catch(Exception ex)
        {
            Vender=null;
            System.out.println("loi"+ex);
        }
        
        return Vender;
    }
    
    
    
    
    
    public static  Vender InfomationVender(String  VenderId)
    {
        Vender vdr = new Vender();
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = ("Select * from Vender where VenderId= ?");
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, VenderId);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                vdr.setVenderId(rs.getInt("VenderId"));
                vdr.setVenderName(rs.getString("VenderName"));
                vdr.setAddress(rs.getString("Address"));
                vdr.setEmail(rs.getString("Email"));
                vdr.setPhone(rs.getString("Phone"));
            }
        }
        catch(Exception ex)
        {
            vdr = null;
        }
        return vdr;
    }

    public boolean  AddVD()
    {
        boolean kq = true;
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "Insert into Vender values(?,?,?,?)";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, this.VenderName);
            ps.setString(2, this.Address);
            ps.setString(3, this.Email);
            ps.setString(4, this.Phone);
            kq = ps.executeUpdate()>0;
        }
        catch(Exception ex)
        {
            System.out.println("loi o day:"+ex);
            kq = false;
        }
        return kq;
    }
    
    public boolean DeleteVD(int venderid)
    {
        boolean kq = true;
        
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "delete from Vender where VenderId=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setInt(1, venderid);
            kq = ps.executeUpdate() > 0;
            
        }
        catch(Exception ex)
        {
            kq = false;
        }
        
        return kq;
    }
    
    public boolean EditVender(int id)
    {
        boolean kq = true;
        try
        {
               Connection con = ConecttoDatabase.getConnection();
               String query = ("Update Vender set VenderName='"+this.VenderName+"' , Address='"+this.Address+"' , Email='"+this.Email+"' , Phone='"+this.Phone+"' where VenderId='"+VenderId+"' ");
              
               Statement st = con.createStatement();
               kq=st.executeUpdate(query)>0;
        }
        catch(Exception ex)
        {
            System.out.println(ex);
            kq = false;
        }
        return kq;
    }

    public static  Vender KiemTraTrung(String chuoi )
    {
        Vender vd = new Vender();
        boolean b = false;
        int count = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select Address from Vender where VenderName='"+chuoi+"'";
            Statement st =  con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                vd.setAddress(rs.getString("Address"));
                count =1;
            }
            if(count==0)
            {
                throw  new Exception();
            }
        } catch (Exception e) {
            vd=null;
            System.out.println(e);
        }
        return vd;
    }

    public static int Count()
    {
        int co = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = " select count(*) from Vender";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                co += rs.getInt(1);
            }
        } catch (Exception e) {
           
            //System.out.println(e);
        }
        return co;
    }
/* private int VenderId;
    private String VenderName;
    private String Address;
    private String Email;
    private String  Phone;*/
    
 public Vector<Vender> getvender()
    {
        Vector<Vender> lvd=new Vector<Vender>();
        try
        {
            Connection con=ConecttoDatabase.getConnection();
            String query="select * from  Vender ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
            {
                Vender vd=new Vender();
                vd.setVenderId(rs.getInt("VenderId"));
                vd.setVenderName(rs.getString("VenderName"));
                vd.setAddress(rs.getString("Address"));
                vd.setEmail(rs.getString("Email"));
                vd.setPhone(rs.getString("Phone")); 
                lvd.add(vd);
            }
            
        }
        catch(Exception ex)
        {
            lvd=null;
            System.out.println(ex);
        }
        return lvd;
    }   

    
}
